/////// This code cleans the index prices used for calculating mark-to-market losses /////////////////


global raw "Replication_Package\Data\raw\"
global processed "Replication_Package\Data\processed\"
global output "Replication_Package\Output\"

////////////////////////////////////////////////////////////////////////////////
import delimited "${raw}FEDFUNDS.csv", clear 
gen year = substr(date,1,4)
gen month = substr(date,6,2) // this is the rate on the first date of a month 
destring year month,replace 
keep if month == 3 | month == 6 | month== 9 | month==12
gen quarter = month/3
drop date 
gen date = yq(year,quarter)
keep date fedfunds
format date %tq // 2022Q1: 248; 2022Q4: 251 
save "${processed}index_price",replace 


import delimited "${raw}RMBS.csv", clear // https://finance.yahoo.com/quote/SPMB/history?p=SPMB
gen year = substr(date,1,4)
gen month = substr(date,6,2)
gen day = substr(date,-2,2)
destring year month day,replace
drop date
sort year month day 
by year month: keep if _n==1 
keep if month == 3 | month == 6 | month== 9 | month==12
gen quarter = month/3
gen date = yq(year,quarter)
keep date close 
ren close rmbs_etf 
merge 1:1 date using "${processed}index_price"
drop _merge 
save "${processed}index_price",replace 


import excel "${raw}mbs_sp_index.xls", cellrange(A7) firstrow clear //https://www.spglobal.com/spdji/en/indices/fixed-income/sp-us-mortgage-backed-securities-index/#overview
replace Effectivedate = trim(Effectivedate)
gen year = substr(Effectivedate,-4,4)
replace Effectivedate = "0"+Effectivedate if substr(Effectivedate,2,1)=="/"
gen month = substr(Effectivedate,1,2)
gen day = substr(Effectivedate,4,1) if substr(Effectivedate,5,1)=="/"
replace day = substr(Effectivedate,4,2) if substr(Effectivedate,5,1)!="/"
destring year month day,replace force
sort year month day 
by year month: keep if _n==1 
keep if month == 3 | month == 6 | month== 9 | month==12
gen quarter = month/3
gen date = yq(year,quarter)
keep date SPUSMortgageBackedSecurit 
ren SPUSMortgageBackedSecurit rmbs 
merge 1:1 date using "${processed}index_price"
drop _merge 
save "${processed}index_price",replace 

import delimited "${raw}CMBS.csv", clear // https://finance.yahoo.com/quote/CMBS
gen year = substr(date,1,4)
gen month = substr(date,6,2)
gen day = substr(date,-2,2)
destring year month day,replace
drop date
sort year month day 
by year month: keep if _n==1 
keep if month == 3 | month == 6 | month== 9 | month==12
gen quarter = month/3
gen date = yq(year,quarter)
keep date close 
ren close cmbs 
merge 1:1 date using "${processed}index_price"
drop _merge 
save "${processed}index_price",replace 

import excel "${raw}treasury_sp_index.xls", cellrange(A7) firstrow clear // https://www.spglobal.com/spdji/en/indices/fixed-income/sp-us-treasury-bond-index/#overview
replace Effectivedate = trim(Effectivedate)
gen year = substr(Effectivedate,-4,4)
replace Effectivedate = "0"+Effectivedate if substr(Effectivedate,2,1)=="/"
gen month = substr(Effectivedate,1,2)
gen day = substr(Effectivedate,4,1) if substr(Effectivedate,5,1)=="/"
replace day = substr(Effectivedate,4,2) if substr(Effectivedate,5,1)!="/"
destring year month day,replace force
sort year month day 
by year month: keep if _n==1 
keep if month == 3 | month == 6 | month== 9 | month==12
gen quarter = month/3
gen date = yq(year,quarter)
keep date SPUSTreasuryBondIndex 
ren SPUSTreasuryBondIndex treasury 
merge 1:1 date using "${processed}index_price"
drop _merge 
save "${processed}index_price",replace 

// different maturity for treasuries 

import delimited "${raw}treasury_year0to1.csv", clear // https://finance.yahoo.com/quote/IBTU.L?p=IBTU.L&.tsrc=fin-srch
gen year = substr(date,1,4)
gen month = substr(date,6,2)
gen day = substr(date,-2,2)
destring year month day,replace
drop date
sort year month day 
by year month: keep if _n==1 
keep if month == 3 | month == 6 | month== 9 | month==12
gen quarter = month/3
gen date = yq(year,quarter)
keep date close 
ren close treasury_1yr 
merge 1:1 date using "${processed}index_price"
drop _merge 
save "${processed}index_price",replace 


import delimited "${raw}treasury_year1to3.csv", clear 
//https://finance.yahoo.com/quote/SHY/history
gen year = substr(date,1,4)
gen month = substr(date,6,2)
gen day = substr(date,-2,2)
destring year month day,replace
drop date
sort year month day 
by year month: keep if _n==1 
keep if month == 3 | month == 6 | month== 9 | month==12
gen quarter = month/3
gen date = yq(year,quarter)
keep date close 
ren close treasury_1to3 
merge 1:1 date using "${processed}index_price"
drop _merge 
save "${processed}index_price",replace 
 
import excel "${raw}treasury_year3to5.xls", cellrange(A7) firstrow clear // https://www.spglobal.com/spdji/en/indices/fixed-income/sp-us-treasury-bond-3-5-year-index/#overview
replace Effectivedate = trim(Effectivedate)
gen year = substr(Effectivedate,-4,4)
replace Effectivedate = "0"+Effectivedate if substr(Effectivedate,2,1)=="/"
gen month = substr(Effectivedate,1,2)
gen day = substr(Effectivedate,4,1) if substr(Effectivedate,5,1)=="/"
replace day = substr(Effectivedate,4,2) if substr(Effectivedate,5,1)!="/"
destring year month day,replace force
sort year month day 
by year month: keep if _n==1 
keep if month == 3 | month == 6 | month== 9 | month==12
gen quarter = month/3
gen date = yq(year,quarter)
keep date SPUSTreasuryBond35Year 
ren SPUSTreasuryBond35Year treasury_3to5 
merge 1:1 date using "${processed}index_price"
drop _merge 
save "${processed}index_price",replace 


import delimited "${raw}treasury_year7to10.csv", clear // https://finance.yahoo.com/quote/IEF/history?p=IEF
gen year = substr(date,1,4)
gen month = substr(date,6,2)
gen day = substr(date,-2,2)
destring year month day,replace
drop date
sort year month day 
by year month: keep if _n==1 
keep if month == 3 | month == 6 | month== 9 | month==12
gen quarter = month/3
gen date = yq(year,quarter)
keep date close 
ren close treasury_7to10 
merge 1:1 date using "${processed}index_price"
drop _merge 
save "${processed}index_price",replace 

import delimited "${raw}treasury_year10to20.csv", clear // ishare https://finance.yahoo.com/quote/TLH/history?p=TLH
gen year = substr(date,1,4)
gen month = substr(date,6,2)
gen day = substr(date,-2,2)
destring year month day,replace
drop date
sort year month day 
by year month: keep if _n==1 
keep if month == 3 | month == 6 | month== 9 | month==12
gen quarter = month/3
gen date = yq(year,quarter)
keep date close 
ren close treasury_10to20
merge 1:1 date using "${processed}index_price"
drop _merge 
save "${processed}index_price",replace 

import delimited "${raw}treasury_year20plus.csv", clear // ishare https://finance.yahoo.com/quote/TLT/history?p=TLT
gen year = substr(date,1,4)
gen month = substr(date,6,2)
gen day = substr(date,-2,2)
destring year month day,replace
drop date
sort year month day 
by year month: keep if _n==1 
keep if month == 3 | month == 6 | month== 9 | month==12
gen quarter = month/3
gen date = yq(year,quarter)
keep date close 
ren close treasury_20plus 
merge 1:1 date using "${processed}index_price"
drop _merge 
save "${processed}index_price",replace

import excel "${raw}treasury_sp_index.xls", cellrange(A7) firstrow clear // https://www.spglobal.com/spdji/en/indices/fixed-income/sp-us-treasury-bond-index/#overview
replace Effectivedate = trim(Effectivedate)
gen year = substr(Effectivedate,-4,4)
replace Effectivedate = "0"+Effectivedate if substr(Effectivedate,2,1)=="/"
gen month = substr(Effectivedate,1,2)
gen day = substr(Effectivedate,4,1) if substr(Effectivedate,5,1)=="/"
replace day = substr(Effectivedate,4,2) if substr(Effectivedate,5,1)!="/"
destring year month day,replace force
sort year month day 
by year month: keep if _n==1 
keep if month == 3 | month == 6 | month== 9 | month==12
gen quarter = month/3
gen date = yq(year,quarter)
keep date SPUSTreasuryBondIndex 
ren SPUSTreasuryBondIndex treasury 
merge 1:1 date using "${processed}index_price"
drop _merge 
save "${processed}index_price",replace 


